__author__ = 'nvsmirnov'
# -*- coding: utf-8 -*-

import psycopg2
import datetime
from Framework.DataClasses.CommonData import CommonData

class createDatabaseData(object):

    #Объявим даты проведения:
    #дата добавления (date_added) текущая
    cur_date = datetime.date.today()
    print "date_added: ", cur_date
    #date_published
    date_published = datetime.datetime.now()
    print "date_published: ", date_published
    #date_end_registration = date_published + 1 сутки
    date_end_registration = date_published + datetime.timedelta(days=1)
    print "date_end_registration: ", date_end_registration
    #date_end_first_part_review = date_published + 2 суток
    date_end_first_part_review = date_published + datetime.timedelta(days=2)
    print "date_end_first_part_review: ", date_end_first_part_review
    #date_begin_auction
    date_begin_auction = date_published + datetime.timedelta(days=3)
    print "date_begin_auction: ", date_begin_auction

    #Для тестов подачи заявок на аукцион
    def insertAuction2_3_4(self, cur_date=cur_date, date_published=date_published, date_end_registration=date_end_registration, date_end_first_part_review=date_end_first_part_review, date_begin_auction=date_begin_auction):
        #создать соединение
        connect = psycopg2.connect(host='localhost', port="8888", database="etp", user="edinews_viewer", password="pgedinews")
        cursor = connect.cursor()

        #Добавить запись в таблицу аукционов
        cursor.execute("""INSERT INTO auctions
                        (registry_number,
                        title,
                        organizer_customer_id,
                        email,
                        phones,
                        date_added,
                        date_published,
                        date_end_registration,
                        date_end_first_part_review,
                        date_begin_auction,
                        status,
                        version,
                        organizer_user_id,
                        region_io_id)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""",
                    ("AUT11111111111", "autotest2_3_4", 48261, "auto@auto.com", "+7-495-5555555", cur_date, date_published, date_end_registration, date_end_first_part_review, date_begin_auction, 3, 1, 112878, 7))
        #Закомитить транзакцию
        connect.commit()
        #Вернуть id добавленого аукциона
        auction_id = cursor.fetchone()

        #Добавить запись в таблицу auction_customers
        cursor.execute("""INSERT INTO auction_customers
                        (auction_id,
                        customer_id)
                        VALUES (%s, %s);""", (auction_id, 48261)
                    )
        connect.commit()

        #Добавить начальную стоимость в таблицу lots
        cursor.execute("""INSERT INTO lots
                        (auction_id,
                        price,
                        currency_id,
                        bik_app,
                        guarantee_sum)
                        VALUES (%s, %s, %s, %s, %s);""", (auction_id, 566000.00, 'RUR', '049706609', 25000.00)
                    )

        connect.commit()


        #преобразуем в строку
        auction_id = str(auction_id[0])

        cursor.close()
        connect.close()
        return auction_id

    def delete_auction(self, delete_id):
        """
        Метод удаления аукциона
        Подходит для всех состояний
        :param delete_id:
        :return:
        """
        print "Удалить аукцион с id: ", delete_id
        #создать соединение
        connect = psycopg2.connect(host=CommonData.host, port=CommonData.port, database=CommonData.database, user=CommonData.user, password=CommonData.password)
        cursor = connect.cursor()

        #Удалить аукцион
        cursor.execute("DELETE FROM auction_customers WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM commission_decision WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM applications WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM offers WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM contracts WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM lots WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM protocols WHERE auction_id = %s;", [delete_id])
        connect.commit()
        cursor.execute("DELETE FROM auctions WHERE id = %s;", [delete_id])
        connect.commit()

        cursor.close()
        connect.close()

    def insertAuction5(self, cur_date=cur_date, date_published=date_published, date_end_registration=date_end_registration, date_end_first_part_review=date_end_first_part_review, date_begin_auction=date_begin_auction):
        #создать соединение
        connect = psycopg2.connect(host='localhost', port="8888", database="etp", user="edinews_viewer", password="pgedinews")
        cursor = connect.cursor()

        cursor.execute("""INSERT INTO auctions
                        (registry_number,
                        title,
                        organizer_customer_id,
                        email,
                        phones,
                        date_added,
                        date_published,
                        date_end_registration,
                        date_end_first_part_review,
                        date_begin_auction,
                        status,
                        version,
                        organizer_user_id,
                        region_io_id)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""",
                       ("AUT11111111111", "autotest2_3_4", 48261, "auto@auto.com", "+7-495-5555555", cur_date, date_published, date_end_registration, date_end_first_part_review, date_begin_auction, 4, 1, 112878, 7))
        #Закомитить транзакцию
        connect.commit()

        #Вернуть id добавленого аукциона
        auction_id = cursor.fetchone()

        cursor.execute("""INSERT INTO auction_customers
                        (auction_id,
                        customer_id)
                        VALUES (%s, %s);""", (auction_id, 48261)
                       )
        connect.commit()

        #Добавить начальную стоимость в таблицу lots
        cursor.execute("""INSERT INTO lots
                        (auction_id,
                        price,
                        currency_id,
                        bik_app,
                        guarantee_sum)
                        VALUES (%s, %s, %s, %s, %s) RETURNING id;""", (auction_id, 566000.00, 'RUR', '049706609', 25000.00)
                    )
        connect.commit()

        #Получить id лота
        lot_id = cursor.fetchone()
        print "Id лота: ", lot_id

        """
        !!!!!
        Добавить 2 записи в таблицу applications, что есть 2 заявки на аукцион.
        Добавляются от 2х пользователей Andrew5(creator_user_id = 113800) и Andrew6(creator_user_id = 113801)
        """

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113800, 1, datetime.datetime.now(), 86118, 65582, 111))
        connect.commit()

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113801, 1, datetime.datetime.now(), 86119, 65582, 222))
        connect.commit()


        #преобразовать в строку
        auction_id = str(auction_id[0])

        cursor.close()
        connect.close()
        return auction_id



    def insertAuction6(self):
        #Определим даты для Торгов
        date_added = datetime.date.today() - datetime.timedelta(days=3)
        print "date_added: ", date_added

        #date_published
        date_published = datetime.datetime.now() - datetime.timedelta(days=3)
        print "date_published: ", date_published

        #date_end_registration
        date_end_registration = datetime.datetime.now() - datetime.timedelta(days=2)
        print "date_end_registration: ", date_end_registration

        #date_end_first_part_review
        date_end_first_part_review = datetime.datetime.now() - datetime.timedelta(days=1)
        print "date_end_first_part_review: ", date_end_first_part_review

        #date_begin_auction
        date_begin_auction = datetime.datetime.now()
        print "date_begin_auction: ", date_begin_auction

        #создать соединение
        connect = psycopg2.connect(host='localhost', port="8888", database="etp", user="edinews_viewer", password="pgedinews")
        cursor = connect.cursor()

        cursor.execute("""INSERT INTO auctions
                        (registry_number,
                        title,
                        organizer_customer_id,
                        email,
                        phones,
                        date_added,
                        date_published,
                        date_end_registration,
                        date_end_first_part_review,
                        date_begin_auction,
                        status,
                        version,
                        organizer_user_id,
                        region_io_id)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""",
                       ("AUT11111111111", "autotest2_3_4", 48261, "auto@auto.com", "+7-495-5555555", date_added, date_published, date_end_registration, date_end_first_part_review, date_begin_auction, 5, 1, 112878, 7))
        #Закомитить транзакцию
        connect.commit()

        #Вернуть id добавленого аукциона
        auction_id = cursor.fetchone()

        cursor.execute("""INSERT INTO auction_customers
                        (auction_id,
                        customer_id)
                        VALUES (%s, %s);""", (auction_id, 48261)
                       )
        connect.commit()

        #Добавить начальную стоимость в таблицу lots
        cursor.execute("""INSERT INTO lots
                        (auction_id,
                        price,
                        currency_id,
                        bik_app,
                        guarantee_sum)
                        VALUES (%s, %s, %s, %s, %s) RETURNING id;""", (auction_id, 566000.00, 'RUR', '049706609', 25000.00)
                    )
        connect.commit()

        #Получить id лота
        lot_id = cursor.fetchone()
        print "Id лота: ", lot_id

        """
        !!!!!
        Добавить 2 записи в таблицу applications, что есть 2 заявки на аукцион.
        Добавляются от 2х пользователей Andrew5(creator_user_id = 113800) и Andrew6(creator_user_id = 113801)
        """

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113800, 3, datetime.datetime.now(), 86118, 65582, 111))
        connect.commit()


        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113801, 3, datetime.datetime.now(), 86119, 65582, 222))
        connect.commit()


        #преобразовать в строку
        auction_id = str(auction_id[0])

        cursor.close()
        connect.close()
        return auction_id


    def insertAuction6(self):
        #Определим даты для Торгов
        date_added = datetime.date.today() - datetime.timedelta(days=3)
        print "date_added: ", date_added

        #date_published
        date_published = datetime.datetime.now() - datetime.timedelta(days=3)
        print "date_published: ", date_published

        #date_end_registration
        date_end_registration = datetime.datetime.now() - datetime.timedelta(days=2)
        print "date_end_registration: ", date_end_registration

        #date_end_first_part_review
        date_end_first_part_review = datetime.datetime.now() - datetime.timedelta(days=1)
        print "date_end_first_part_review: ", date_end_first_part_review

        #date_begin_auction
        date_begin_auction = datetime.datetime.now()
        print "date_begin_auction: ", date_begin_auction

        #создать соединение
        connect = psycopg2.connect(host='localhost', port="8888", database="etp", user="edinews_viewer", password="pgedinews")
        cursor = connect.cursor()

        cursor.execute("""INSERT INTO auctions
                        (registry_number,
                        title,
                        organizer_customer_id,
                        email,
                        phones,
                        date_added,
                        date_published,
                        date_end_registration,
                        date_end_first_part_review,
                        date_begin_auction,
                        status,
                        version,
                        organizer_user_id,
                        region_io_id)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""",
                       ("AUT11111111111", "autotest2_3_4", 48261, "auto@auto.com", "+7-495-5555555", date_added, date_published, date_end_registration, date_end_first_part_review, date_begin_auction, 5, 1, 112878, 7))
        #Закомитить транзакцию
        connect.commit()

        #Вернуть id добавленого аукциона
        auction_id = cursor.fetchone()

        cursor.execute("""INSERT INTO auction_customers
                        (auction_id,
                        customer_id)
                        VALUES (%s, %s);""", (auction_id, 48261)
                       )
        connect.commit()

        #Добавить начальную стоимость в таблицу lots
        cursor.execute("""INSERT INTO lots
                        (auction_id,
                        price,
                        currency_id,
                        bik_app,
                        guarantee_sum)
                        VALUES (%s, %s, %s, %s, %s) RETURNING id;""", (auction_id, 566000.00, 'RUR', '049706609', 25000.00)
                    )
        connect.commit()

        #Получить id лота
        lot_id = cursor.fetchone()
        print "Id лота: ", lot_id

        """
        !!!!!
        Добавить 2 записи в таблицу applications, что есть 2 заявки на аукцион.
        Добавляются от 2х пользователей Andrew5(creator_user_id = 113800) и Andrew6(creator_user_id = 113801)
        """

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113800, 3, datetime.datetime.now(), 86118, 65582, 111))
        connect.commit()


        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113801, 3, datetime.datetime.now(), 86119, 65582, 222))
        connect.commit()


        #преобразовать в строку
        auction_id = str(auction_id[0])

        cursor.close()
        connect.close()
        return auction_id


    def insertAuction8(self, cur_date=cur_date, date_published=date_published, date_end_registration=date_end_registration, date_end_first_part_review=date_end_first_part_review, date_begin_auction=date_begin_auction):
        #Аукцион уже свершился
        date_begin_auction = datetime.datetime.now() - datetime.timedelta(hours=2)

        #создать соединение
        connect = psycopg2.connect(host='localhost', port="8888", database="etp", user="edinews_viewer", password="pgedinews")
        cursor = connect.cursor()

        cursor.execute("""INSERT INTO auctions
                        (registry_number,
                        title,
                        organizer_customer_id,
                        email,
                        phones,
                        date_added,
                        date_published,
                        date_end_registration,
                        date_end_first_part_review,
                        date_begin_auction,
                        status,
                        version,
                        organizer_user_id,
                        region_io_id)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""",
                       ("AUT11111111111", "autotest2_3_4", 48261, "auto@auto.com", "+7-495-5555555", cur_date, date_published, date_end_registration, date_end_first_part_review, date_begin_auction, 6, 1, 112878, 7))
        #Закомитить транзакцию
        connect.commit()

        #Вернуть id добавленого аукциона
        auction_id = cursor.fetchone()

        cursor.execute("""INSERT INTO auction_customers
                        (auction_id,
                        customer_id)
                        VALUES (%s, %s);""", (auction_id, 48261)
                       )
        connect.commit()

        #Добавить начальную стоимость в таблицу lots
        cursor.execute("""INSERT INTO lots
                        (auction_id,
                        price,
                        currency_id,
                        bik_app,
                        guarantee_sum,
                        guarantee_contract)
                        VALUES (%s, %s, %s, %s, %s, %s) RETURNING id;""", (auction_id, 566000.00, 'RUR', '049706609', 25000.00, True)
                    )
        connect.commit()

        #Получить id лота
        lot_id = cursor.fetchone()
        print "Id лота: ", lot_id

        """
        !!!!!
        Добавить 2 записи в таблицу applications, что есть 2 заявки на аукцион.
        Добавляются от 2х пользователей Andrew5(creator_user_id = 113800) и Andrew6(creator_user_id = 113801)
        """

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113800, 3, datetime.datetime.now(), 86118, 65582, 111))
        connect.commit()

        app_id = cursor.fetchone()

        cursor.execute("""INSERT INTO applications
                        (lot_id,
                        auction_id,
                        date_added,
                        eds_signed_text,
                        xml_signature,
                        creator_user_id,
                        status,
                        date_published,
                        supplier_id,
                        order_number_random,
                        order_number_added)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;""", (lot_id, auction_id, datetime.datetime.now(), '1', '1', 113801, 3, datetime.datetime.now(), 86119, 65582, 222))
        connect.commit()
        app_id1 = cursor.fetchone()

        #Добавить запись в таблицу offers, ставка от andrew6
        cursor.execute("""INSERT INTO offers
                        (lot_id,
                        auction_id,
                        application_id,
                        date_added,
                        price,
                        user_id,
                        eds_signed_text)
                        VALUES(%s, %s, %s, %s, %s, %s, %s);""", (lot_id, auction_id, app_id, datetime.datetime.now(), 450000, 113801, '1'))
        connect.commit()

        #Добавить запись в таблицу offers, ставка от andrew5
        cursor.execute("""INSERT INTO offers
                        (lot_id,
                        auction_id,
                        application_id,
                        date_added,
                        price,
                        user_id,
                        eds_signed_text)
                        VALUES(%s, %s, %s, %s, %s, %s, %s);""", (lot_id, auction_id, app_id1, datetime.datetime.now(), 500000, 113800, '1'))
        connect.commit()

        #преобразовать в строку
        auction_id = str(auction_id[0])

        cursor.close()
        connect.close()
        return auction_id